var mailDao={
	tableName:"mail",
    countSendMails:function(userId) {
        var sql = "select count(*) from " + this.tableName //
                + " where isDeleteS=0 and senderId=:uid";
        var ds = this.db().query(sql, "uid", userId);
        return Dt.toInt(ds.getCell(0, 0), 0);
    },

    countReceiveMails:function(userId) {
        var sql = "select count(*) from " + this.tableName //
                + " where isDeleteR=0 and receiverId=:uid";
        var ds = this.db().query(sql, "uid", userId);
        return Dt.toInt(ds.getCell(0, 0), 0);
    },

    loadSendMails:function(userId,  first, size) {
        var sql = "select id,sysId,isSys,isTrade,itemNum,money " //
                + ",isReaded,isFetched,isSendb,isDeleteS,isDeleteR " //
                + ",title,sendTime,senderId,receiverId,senderName,receiverName " //
                + "from " + this.tableName //
                + " where isDeleteS=0 and senderId=:uid order by sendTime desc " //
                + "limit " + first + "," + size;
        var ds = this.db().query(sql, "uid", userId);
        return toMailHeaders(ds);
    },

    loadReceiveMails:function( userId,  tag,  first,  size) {
        if (tag == 1) {
            var sql = "select id,sysId,isSys,isTrade,itemNum,money " //
                    + ",isReaded,isFetched,isSendb,isDeleteS,isDeleteR " //
                    + ",title,sendTime,senderId,receiverId,senderName,receiverName "//
                    + "from " + this.tableName //
                    + " where isReaded=0 and isDeleteR=0 and receiverId=:uid order by sendTime desc " //
                    + "limit " + first + "," + size;
            var ds = this.db().query(sql, "uid", userId);
            var mails = this.toMailHeaders(ds);
            // logger.debug(sql);
            // logger.debug("unreaded size:" + mails.size());
            if (mails.size() < size) {
                var first2 = first - this.countUnreadedReceiveMails(userId);
                if (first2 < 0) {
                    first2 = 0;
                }
                var size2 = size - mails.size();
                sql = "select id,sysId,isSys,isTrade,itemNum,money " //
                        + ",isReaded,isFetched,isSendb,isDeleteS,isDeleteR " //
                        + ",title,sendTime,senderId,receiverId,senderName,receiverName " + " from " + this.tableName //
                        + " where isReaded=1 and isDeleteR=0 and receiverId=:uid order by sendTime desc " //
                        + "limit " + first2 + "," + size2;
                // logger.debug(sql);
                ds = this.db().query(sql, "uid", userId);
                // logger.debug("readed size:" + ds.getRowCount());
                mails.addAll(toMailHeaders(ds));
            }
            return mails;
        } else {
            var sql = "select id,sysId,isSys,isTrade,itemNum,money " //
                    + ",isReaded,isFetched,isSendb,isDeleteS,isDeleteR " //
                    + ",title,sendTime,senderId,receiverId,senderName,receiverName "//
                    + "from " + this.tableName //
                    + " where isDeleteR=0 and receiverId=:uid order by sendTime desc " //
                    + "limit " + first + "," + size;
            var ds = this.db().query(sql, "uid", userId);
            return toMailHeaders(ds);
        }
    },

    countUnreadedReceiveMails:function( userId) {
        var sql = "select count(*) from " + this.tableName //
                + " where isReaded=0 and isDeleteR=0 and receiverId=:uid";
        return Dt.toInt(db().query(sql, "uid", userId).getCell(0, 0), 0);
    },

    loadMail:function(mailId) {
        var sql = "select id,sysId,isSys,isTrade,itemNum,money " //
                + ",isReaded,isFetched,isSendb,isDeleteS,isDeleteR " //
                + ",title,sendTime,senderId,receiverId,senderName,receiverName " //
                + ",content,sysmsg,itemsStr,tax,cbInfo " //
                + " from " + this.tableName + " where id=:mid ";
        var ds = this.db().query(sql, "mid", mailId);
        if (ds.isEmpty()) {
            return null;
        }
        return this.toMailDetail(ds, 0);
    },

    // =================
    addMail:function( mail) {
        if (mail.getSysId() != null) {// 系统邮件
            var sql = "insert into " + this.tableName //
                    + "(isSys,isDeleteS,sysId,receiverId,receiverName,sendTime) "//
                    + "values(?,?,?,?,?,?,?)";
            this.db().update(sql, true, true, mail.getSysId(), //
                    mail.getReceiverId(), mail.getReceiverName(), new JavaDate(mail.getSendTime()));
// mail.setId();
        } else {
            var sql = "insert into " + this.tableName //
                    + "(isSys,isTrade,itemNum " //
                    + ",isReaded,isFetched,isSendb,isDeleteS,isDeleteR "//
                    + ",title,sendTime,senderId,receiverId,senderName,receiverName " //
                    + ",content,sysmsg,itemsStr,money,tax,cbInfo) " //
                    + "values(:isSys,:isT,:in,:isR,:isF,:isSb,:isDs,:isDr"//
                    + ",:tit,:st,:sid,:rid,:sn,:rn,:cont,:sm,:is,:mon,:tax,:cb)";
            this.db().update(sql, //
                    "isSys", mail.isSystemMail(), "isT", mail.isTrade(), "in", mail.getItemListSize(), //
                    "isR", mail.isReaded(), "isF", mail.isFetched(), "isSb", mail.isSendBack(), //
                    "isDs", mail.isDeleteS(), "isDr", mail.isDeleteR(), //
                    "tit", mail.getTitle(), "st", new Date(mail.getSendTime()),//
                    "sid", mail.getSenderId(), "rid", mail.getReceiverId(),//
                    "sn", mail.getSenderName(), "rn", mail.getReceiverName(),//
                    "cont", mail.getContent(), "sm", mail.getSysmsg(), //
                    "is", JavaJSON.toJSONString(mail.getItemsInfo(), false),//
                    "mon", mail.getMoney(), "tax", mail.getTax(), //
                    "cb", JavaJSON.toJSONString(mail.getCallbackInfo(), false));
// mail.setId();
        }
    },

    updateMailStatus:function( mail) {
        var sql = "update " + this.tableName //
                + " set isReaded=:isR,isFetched=:isF,isSendb=:isSb "//
                + ",isDeleteS=:isDs,isDeleteR=:isDr "//
                + "where id=:mid";
        this.db().update(sql, //
                "isR", mail.isReaded(), "isF", mail.isFetched(), "isSb", mail.isSendBack(),//
                "isDs", mail.isDeleteS(), "isDr", mail.isDeleteR(),//
                "mid", mail.getId());
    },

    deleteMail:function( mail) {
        var sql = "delete from " + this.tableName + " where id=:mid ";
        this.db().update(sql, "mid", mail.getId());
    },

    markMailDelete:function( mail) {
        var sql = "update " + this.tableName + " set isDeleteS=1,isDeleteR=1 where id=:mid ";
        this.db().update(sql, "mid", mail.getId());
    },

    getSendbackExpiredMails:function( size,  expiredTime) {
        var sql = "select id,sysId,isSys,isTrade,itemNum,money " //
                + ",isReaded,isFetched,isSendb,isDeleteS,isDeleteR " //
                + ",title,sendTime,senderId,receiverId,senderName,receiverName "//
                + ",content,sysmsg,itemsStr,tax from " + this.tableName //
                + " where isTrade=1 and isFetched=0 and sendTime<=:st";
        var sendTime = System.currentTimeMillis() - expiredTime;
        var ds = this.db().query(sql, "st", sendTime);
        return this.toMailDetails(ds);
    },

    getDeleteExpiredMails:function( size, expiredTime) {
        var sql = "select id,sysId,isSys,isTrade,itemNum,money " //
                + ",isReaded,isFetched,isSendb,isDeleteS,isDeleteR " //
                + ",title,sendTime,senderId,receiverId,senderName,receiverName "//
                + ",content,sysmsg,itemsStr,tax from  " + this.tableName//
                + "where isTrade=0 and isFetched=0 and sendTime<=:st";
        var sendTime = System.currentTimeMillis() - expiredTime;
        var ds = this.db().query(sql, "st", sendTime);
        return this.toMailDetails(ds);
    },

    markDeleteExpiredMails:function( expiredTime) {
        var sql = "update " + this.tableName //
                + " set isDeleteS=1,isDeleteR=1 "//
                + "where (isDeleteS=0 or isDeleteR=0) and isSysOp=0 and (isTrade=0 or (isTrade=1 and isFetched=1)) and sendTime<=:st";
        var sendTime = System.currentTimeMillis() - expiredTime;
        this.db().update(sql, "st", sendTime);
    },

    cleanMails:function() {
        var sql = "delete from mail_data where isSysOp=0 and isDeleteS=1 and isDeleteR=1";
        this.db().update(sql);
    },

    toMailHeader:function( ds,  row) {
        var mail = new Mail();
        mail.setId(Dt.toLong(ds.getCell(row, "id"), 0));
        mail.setSystemMail(Dt.toBoolean(ds.getCell(row, "isSys"), false));
        mail.setReaded(Dt.toBoolean(ds.getCell(row, "isReaded"), false));
        mail.setFetched(Dt.toBoolean(ds.getCell(row, "isFetched"), false));
        mail.setDeleteS(Dt.toBoolean(ds.getCell(row, "isDeleteS"), false));
        mail.setDeleteR(Dt.toBoolean(ds.getCell(row, "isDeleteR"), false));
        mail.setSendTime(Dt.toLong(ds.getCell(row, "sendTime"), 0));
        mail.setReceiverId(Dt.toString(ds.getCell(row, "receiverId"), null));
        mail.setReceiverName(Dt.toString(ds.getCell(row, "receiverName"), null));
        //
        mail.setSysId(Dt.toString(ds.getCell(row, "sysId"), null));
        if (mail.getSysId() != null) {// 系统邮件
            var sysMail = new Mail();// TODO
            mail.setTitle(sysMail.getTitle());
            mail.setContent(sysMail.getContent());
            mail.setItemsInfo(sysMail.getItemsInfo());
            mail.setItemListSize(sysMail.getItemsInfo().size());
            mail.setMoney(sysMail.getMoney());
        } else {
            mail.setTrade(Dt.toBoolean(ds.getCell(row, "isTrade"), false));
            mail.setItemListSize(Dt.toInt(ds.getCell(row, "itemNum"), 0));
            mail.setMoney(Dt.toLong(ds.getCell(row, "money"), 0));
            //
            mail.setSendBack(Dt.toBoolean(ds.getCell(row, "isSendb"), false));
            //
            mail.setTitle(Dt.toString(ds.getCell(row, "title"), null));
            mail.setSenderId(Dt.toString(ds.getCell(row, "senderId"), null));
            mail.setSenderName(Dt.toString(ds.getCell(row, "senderName"), null));
        }
        return mail;
    },

    toMailHeaders:function( ds) {
        var mails = new ArrayList(ds.getRowCount());
        for (var row = 0; row < ds.getRowCount(); row++) {
            mails.add(this.toMailHeader(ds, row));
        }
        return mails;
    },

    toMailDetails:function( ds) {
        var mails = new ArrayList(ds.getRowCount());
        for (var i = 0; i < ds.getRowCount(); i++) {
            mails.add(this.toMailDetail(ds, i));
        }
        return mails;
    },

    toMailDetail:function( ds,  row) {
        var mail = this.toMailHeader(ds, row);
        mail.setCallbackInfo(JavaJSON.parseObject(Dt.toString(ds.getCell(row, "cbInfo"), null)));
        if (mail.getSysId() == null) {// 系统邮件
            mail.setContent(Dt.toString(ds.getCell(row, "content"), null));
            mail.setSysmsg(Dt.toString(ds.getCell(row, "sysmsg"), null));
            mail.setTax(Dt.toLong(ds.getCell(row, "tax"), 0));
            mail.setItemsInfo(JavaJSON.parseArray(Dt.toString(ds.getCell(row, "itemsStr"), null)));
        }
        return mail;
    },

    // ===============
    db:function () {
        return getDb("main");
    },

    initIfNeed:function() {
        var sql = "create table if not exists " + this.tableName + " ( " //
                + "id bigint(32) unsigned NOT NULL auto_increment, " //
                + "sysId varchar(32) default NULL, " //
                + "title varchar(64) default '', " //
                + "isSendb tinyint(1) default '0', " //
                + "isSys tinyint(1) default '0', " //
                + "isTrade tinyint(1) default '0', " //
                + "isReaded tinyint(1) default '0', " //
                + "isDeleteS tinyint(1) default '0', " //
                + "isDeleteR tinyint(1) default '0', " //
                + "isFetched tinyint(1) default '0', " //
                + "isSysOp tinyint(1) default '0', "//
                + "content varchar(255) default '', " //
                + "sysmsg varchar(50) default '', "//
                + "sendTime datetime default NULL, "//
                + "senderId varchar(32) default '', "//
                + "receiverId varchar(32) default '', "//
                + "senderName varchar(32) default '', "//
                + "receiverName varchar(32) default '', "//
                + "itemsStr longblob default NULL, "//
                + "itemNum int(11) default '0', "//
                + "money int(64) default '0', "//
                + "tax int(64) default '0', "//
                + "cbInfo longblob default NULL, "//
                + "PRIMARY KEY  (id), " //
// + "KEY (senderId), " //
// + "KEY (receiverId), " //
// + "KEY (sendTime) " //
                + ");";
        this.db().update(sql);
    },
};
mailDao.initIfNeed();

